require("lsqlite3")
require("curl")
require("xml")

local db = sqlite3.open("bank.db")
assert(db:exec([[
CREATE TABLE IF NOT EXISTS ranks (
	id INTEGER PRIMARY KEY,
	name TEXT);

CREATE TABLE IF NOT EXISTS bags (
	id INTEGER PRIMARY KEY,
	icon TEXT,
	name TEXT,
	viewable BOOLEAN);

CREATE TABLE IF NOT EXISTS transactions (
	id INTEGER PRIMARY KEY,
	date DATETIME,
	player TEXT,
	rank_id INTEGER,
	dtab INTEGER,
	otab INTEGER,
	money INTEGER,
	type INTEGER,
	FOREIGN KEY (rank_id) REFERENCES ranks(id),
	FOREIGN KEY (dtab) REFERENCES bags(id),
	FOREIGN KEY (otab) REFERENCES bags(id));

CREATE TABLE IF NOT EXISTS items (
	id INTEGER PRIMARY KEY,
	transaction_id INTEGER,
	item_id INTEGER,
	count INTEGER,
	FOREIGN KEY (transaction_id) REFERENCES transactions(id));
]]), db:errmsg())

local function make_writer (data)
	return function (s, len)
		data[#data + 1] = s
		return len
	end
end

local function get_account_and_passwd()
	pcall(dofile, "login.lua")
	local account = arg[1] or LOGIN
	if not account then
		io.stdout:write("Username: ")
		io.stdout:flush()
		account = io.read()
	end
	local password = PWD
	if not password then
		io.stdout:write("Password: ")
		io.stdout:flush()
		local password = io.read()
	end
	return account, password
end

local function login()
	local account, password = get_account_and_passwd()
	assert(account and password)
	local data, header = {}, {}
	local c = curl.easy_init()
	c:setopt(curl.OPT_URL, "https://eu.wowarmory.com/login.xml")
	c:setopt(curl.OPT_SSL_VERIFYPEER, 0)
	c:setopt(curl.OPT_USERAGENT, "Firefox/2.0")
	c:setopt(curl.OPT_WRITEFUNCTION, make_writer(data))
	c:setopt(curl.OPT_HEADERFUNCTION, make_writer(header))
	local fields = string.format("accountName=%s&password=%s", curl.escape(account), curl.escape(password))
	c:setopt(curl.OPT_POSTFIELDS, fields)

	assert(c:perform())
	data = table.concat(data)
	header = table.concat(header)
	local cookie = header:match("Set%-Cookie: (.-)\n")
	return cookie
end

local function get(url, cookie)
	local data = {}
	local c = curl.easy_init()
	c:setopt(curl.OPT_URL, url)
	c:setopt(curl.OPT_USERAGENT, "Firefox/2.0")
	if cookie then
		c:setopt(curl.OPT_HTTPHEADER, {cookie})
	end
	c:setopt(curl.OPT_WRITEFUNCTION, make_writer (data))
	local err, info = c:perform()
	if err ~= 0 then
		return
	else
		data = table.concat(data)
		if data:len() > 0 then
			return data
		end
	end
end

local statements = setmetatable({}, {__index = function (table, stmt)
	local t = assert(db:prepare(stmt), db:errmsg())
	table[stmt] = t
	return t
end})

local function apply_stmt(s, ...)
	local stmt = s
	if type(s) == "string" then
		stmt = statements[s]
	end
	stmt:bind_values(...)
	local r = stmt:step()
	if r ~= sqlite3.DONE then
		local msg = string.format("Error in apply_stmt(%q) (%s): %s", s, r, db:errmsg())
		error(msg, 2)
	end
	stmt:reset()
end

local cached_sdb
do
	
	local make_db = function (table)
		local select_stmt = db:prepare(string.format("SELECT id FROM %s WHERE name = ?", table))
		local insert_stmt = db:prepare(string.format("INSERT INTO %s (name) VALUES (?)", table))
		return setmetatable({}, {
			__index = function (table, column)
				select_stmt:bind_values(column)
				local r = select_stmt:step()
				if r == sqlite3.ROW then
					r = select_stmt:get_value(0)
				else
					apply_stmt(insert_stmt, column)
					r = db:last_insert_rowid()
				end
				select_stmt:reset()
				r = tonumber(r)
				table[column] = r
				return r
			end,
		})
	end
	
	cached_sdb = setmetatable({}, {
		__index = function (table, column)
			local r = make_db (column)
			table[column] = r
			return r
		end,
	})
end

local function getguildbank(guild, realm)
	local cookie = login()
	assert(cookie, "Connection failed")
	local url = string.format("http://eu.wowarmory.com/guild-bank-log.xml?r=%s&n=%s",
		curl.escape(realm or "Dalaran"), curl.escape(guild))
	return xml.parse(get(url, "Cookie: "..cookie))
end

local function save_ranks_and_bags(page)

	for rank in page:get_first_child("guildRanks"):iterate_children("rank") do
		local name = rank:get_attribute("name")
		local id = tonumber(rank:get_attribute("id"))
		apply_stmt("INSERT INTO ranks VALUES (?, ?)", id, name)
	end
	
	local bank = page:get_first_child("guildBank")
	for bag in bank:get_first_child("bags"):iterate_children("bag") do
		local id = tonumber(bag:get_attribute("id"))
		local icon = bag:get_attribute("icon")
		local name = bag:get_attribute("name")
		local viewable = (bag:get_attribute("viewable") == "true")
		apply_stmt("INSERT INTO bags VALUES (?, ?, ?, ?)", id, icon, name, viewable)
	end
end

local function save_logs(page)
	local stmt = db:prepare("SELECT COUNT(*) FROM transactions WHERE date = datetime(?  / 1000, 'unixepoch', 'localtime')")
	local bank = page:get_first_child("guildBank")
	for log in bank:get_first_child("banklogs"):iterate_children("banklog") do
		local ts = tonumber(log:get_attribute("ts"))
		stmt:bind_values(ts)
		stmt:step()
		local count = stmt:get_value(0)
		stmt:reset()
		if count == 0 then
			apply_stmt("INSERT INTO transactions (date, player, rank_id, dtab, otab, money, type) VALUES (datetime(?  / 1000, 'unixepoch', 'localtime'), ?, ?, ?, ?, ?, ?)",
				ts, 
				log:get_attribute("player"), 
				tonumber(log:get_attribute("rank")),
				tonumber(log:get_attribute("dtab")),
				tonumber(log:get_attribute("otab")),
				tonumber(log:get_attribute("money")),
				tonumber(log:get_attribute("type")))
			local id = db:last_insert_rowid()
			for item in log:iterate_children("item") do
				apply_stmt("INSERT INTO items (transaction_id, item_id, count) VALUES (?, ?, ?)",
					id, tonumber(item:get_attribute("id")), tonumber(item:get_attribute("count")))
			end
		end
	end
end

local data = getguildbank("Ordre du Cristal")
--~ save_ranks_and_bags(data)
save_logs(data)

